﻿

using System.Data;
using System.Text;
using Core.Library;
using Microsoft.ApplicationBlocks.Data;

namespace DataObject.Members
{
    internal class MembersDAL : DAL
    {
        /***************************************
        = Back End
        -------------------------------------- */
        internal static void Members_Update(MembersObject o)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Members_Update", o.Member_ID
                                                            , o.Member_Name
                                                            , o.Member_FullName
                                                            , o.Member_DisplayName
                                                            , o.Member_Age
                                                            , o.Member_Gender
                                                            , o.Member_Birthday
                                                            , o.Member_Email
                                                            , o.Member_Address
                                                            , o.Member_City
                                                            , o.Member_SocialID
                                                            , o.Member_Tel
                                                            , o.Member_About
                                                            , o.Member_Job
                                                            , o.Member_JobCompany
                                                            , o.Member_JobAddress
                                                            , o.Member_JobCity
                                                            , o.Member_JobAbout
                                                            , o.Member_Avatar
                                                            , o.Member_AvataMini
                                                            , o.Member_OriginalAvatar
                                                            , o.Member_Status
                                                            , o.Member_InRound
                                                            , o.Member_RegisterDate
                                                            , o.Vote1
                                                            , o.Vote2
                                                            , o.Vote3
                                                            , o.Company_ID
                                                            , o.isCheatVote
                                                            , o.Member_HomePageFocus
                                                            , o.Member_IsWin
                                                            , o.Member_IsSlide
                                                            , o.Member_AlbumTitle1
                                                            , o.Member_AlbumDesc1
                                                            , o.Member_ContactEmail
                                                            , o.Member_AlbumTitle2
                                                            , o.Member_AlbumDesc2
                                                            , o.Member_AlbumTitle3
                                                            , o.Member_AlbumDesc3
                                                            , o.Member_OriginalAvatar2
                                                            , o.Member_OriginalAvatar3);
        }

        internal static void Members_Update_IsHomePage(int id)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Members_Update_IsHomePage", id);
        }

        internal static void Members_Update_IsSlide(int id)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Members_Update_IsSlide", id);
        }

        internal static void Members_Update_Status(int id, int status, int round)
        {
            SqlHelper.ExecuteNonQuery(Conn, "Members_Update_Status", id
                                                            , status
                                                            , round);
        }

        internal static void Members_Update_Vote_Cheat(int id, int round, int vote, short cheat)
        {
            SqlHelper.ExecuteNonQuery(Conn, "members_update_vote_cheat", id
                                                            , round
                                                            , vote
                                                            , cheat);
        }

        internal static void Members_Update_AntiClone(int id, bool isClone, short cloneLevel, bool isLockVote)
        {
            SqlHelper.ExecuteNonQuery(Conn, "members_update_anticlone", id
                                                            , isClone
                                                            , cloneLevel
                                                            , isLockVote);
        }

        internal static IDataReader Members_GetById(int id)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 [{0}] ", Constants.Table_Members.Member_ID);
            sb.AppendFormat("[{0}] ", Constants.Table_Members.Member_ID);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Name);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_FullName);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_DisplayName);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Age);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Gender);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Birthday);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Email);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Address);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_City);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_SocialID);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Tel);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_About);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Job);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_JobCompany);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_JobAddress);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_JobCity);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_JobAbout);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Avatar);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AvataMini);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_OriginalAvatar);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_Status);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_LastUpdate);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_RegisterDate);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_InRound);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Vote1);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Vote2);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Vote3);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.isCheatVote);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_HomePageFocus);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_IsWin);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_IsSlide);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumTitle1);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumDesc1);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_ContactEmail);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumTitle2);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumDesc2);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumTitle3);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_AlbumDesc3);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_OriginalAvatar2);
            sb.AppendFormat(",[{0}] ", Constants.Table_Members.Member_OriginalAvatar3);
            sb.AppendFormat("FROM [{0}]", Constants.Table_Members.TableName);
            sb.AppendFormat("WHERE [{0}] = {1}", Constants.Table_Members.Member_ID, id);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static int Members_Count(string kName, string kEmail, int inRound, int status)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT([{0}]) ", Constants.Table_Members.Member_ID);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Members.TableName);
            sb.AppendFormat("WHERE ({3} = {5} AND [{1}] = {3} OR {3} = {4} AND [{1}] = {3} OR [{0}] = {2} AND [{1}] = {3}) ", Constants.Table_Members.Member_InRound, Constants.Table_Members.Member_Status, inRound, status, (int)MemberStatus.IsRemoved, (int)MemberStatus.IsWaitingForPublish);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE N'%{1}%') ", Constants.Table_Members.Member_FullName, kName);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE N'%{1}%') ", Constants.Table_Members.Member_Email, kEmail);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static int Members_GetStatistic(int inRound, int status)
        {
            sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT([{0}]) ", Constants.Table_Members.Member_ID);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Members.TableName);
            sb.AppendFormat("WHERE ({3} = {5} AND [{1}] = {3} OR {3} = {4} AND [{1}] = {3} OR [{0}] = {2} AND [{1}] = {3}) ", Constants.Table_Members.Member_InRound, Constants.Table_Members.Member_Status, inRound, status, (int)MemberStatus.IsRemoved, (int)MemberStatus.IsWaitingForPublish);
            return (int)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader Members_GetListByTypeByStatusPaging(string kName, string kEmail, int inRound, int status, int pageIndex, int pageSize, string order)
        {
            sb = new StringBuilder();
            sb.AppendLine("DECLARE @StartRow INT");
            sb.AppendFormat("SET @StartRow = ({0} - 1) * {1} + 1 ", pageIndex, pageSize);
            sb.AppendLine("DECLARE @EndRow INT");
            sb.AppendFormat("SET @EndRow = {0} * {1}; ", pageIndex, pageSize);
            sb.AppendLine("WITH temp AS");
            sb.AppendLine("(");
            sb.AppendLine("SELECT TOP(@EndRow) * ");
            sb.AppendFormat(", ROW_NUMBER() OVER (ORDER BY [{0}] DESC, {1} ) AS 'RowNumber' ", Constants.Table_Members.Member_InRound, order);
            sb.AppendFormat("FROM [dbo].[{0}] ", Constants.Table_Members.TableName);
            sb.AppendFormat("WHERE ({3} = {5} AND [{1}] = {3} OR {3} = {4} AND [{1}] = {3} OR [{0}] = {2} AND [{1}] = {3}) ", Constants.Table_Members.Member_InRound, Constants.Table_Members.Member_Status, inRound, status, (int)MemberStatus.IsRemoved, (int)MemberStatus.IsWaitingForPublish);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE N'%{1}%') ", Constants.Table_Members.Member_FullName, kName);
            sb.AppendFormat("AND ('{1}' = '' OR [{0}] LIKE N'%{1}%') ", Constants.Table_Members.Member_Email, kEmail);
            sb.AppendLine(")");
            sb.AppendLine("SELECT temp.* FROM temp ");
            sb.AppendLine("WHERE RowNumber BETWEEN @StartRow AND @EndRow");
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        internal static IDataReader City_GetAll()
        {
            sb = new StringBuilder();
            sb.AppendLine("SELECT * ");
            sb.AppendFormat("FROM [{0}]", Constants.Table_City.TableName);
            sb.AppendFormat("ORDER BY [{0}] DESC, ", Constants.Table_City.City_Focus);
            sb.AppendFormat("[{0}] ASC", Constants.Table_City.City_Name);
            return SqlHelper.ExecuteReader(Conn, CommandType.Text, sb.ToString());
        }

        /***************************************
        = Front End
        -------------------------------------- */

        internal static IDataReader Members_GetByEmail(string email)
        {
            return SqlHelper.ExecuteReader(Conn, "afe_Members_GetByEmail", email);
        }

        internal static IDataReader Members_GetByIdStore(int id)
        {
            return SqlHelper.ExecuteReader(Conn, "afe_Members_GetById", id);
        }

        internal static int Members_Insert(MembersObject o)
        {


            var result = SqlHelper.ExecuteScalar(Conn, "afe_Members_Insert", o.Member_Name
                                                                    , o.Member_Email
                                                                    , o.Member_Status
                                                                    , o.Member_Avatar
                                                                    , o.Member_Birthday);

            return ConvertUtility.ConvertTo<int>(result);
        }

        internal static void Members_Insert(string email)
        {
            SqlHelper.ExecuteScalar(Conn, "afe_Members_GetByEmail", email);
        }
    }
}
